package com.isyscore.os.metadata.utils;

import com.google.common.base.Joiner;
import com.google.common.base.Strings;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.execute.Execute;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

import static java.util.Collections.emptyList;

@Slf4j
public class SqlUtil {

    public static List<String> getSqlTableNames(String sql) {
        List<String> tableNameList = emptyList();
        if (Strings.isNullOrEmpty(sql)) {
            return tableNameList;
        }
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            tableNameList = tablesNamesFinder.getTableList(statement);
        } catch (JSQLParserException e) {
            log.error("解析SQL表名发生异常", e);
        }
        return tableNameList;
    }

    public static String replaceSqlPageParam(String sql, long firstParam, long secParam) {
        char[] sqlChars = sql.toCharArray();
        boolean inQuot = false;
        StringBuilder sb = new StringBuilder();
        int hit = 0;
        for (char a : sqlChars) {
            if (a == '\'') {
                inQuot = !inQuot;
                sb.append("'");
            } else if (!inQuot && a == '?') {
                hit++;
                if (hit == 1) {
                    sb.append(firstParam);
                } else if (hit == 2) {
                    sb.append(secParam);
                }
            } else {
                sb.append(a);
            }
        }
        return sb.toString();
    }


    public static String buildSqlWitchSchema(String script, String schema) {
        String[] split = script.split(";");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < split.length; i++) {
            Statement statement = null;
            try {
                String tmp = split[i].trim().toUpperCase();
                if (tmp.startsWith("CALL") || tmp.startsWith("EXEC")) {
                    if (split[i].contains("()")) {
                        split[i] = split[i].replaceAll("\\(\\)", "");
                    }
                }

                statement = CCJSqlParserUtil.parse(split[i]);
                if (statement instanceof CreateTable) {
                    ((CreateTable) statement).setTable(new Table(StringUtils.isNotEmpty(((CreateTable) statement).getTable().getSchemaName()) ? ((CreateTable) statement).getTable().getSchemaName() + "." + ((CreateTable) statement).getTable().getName() : schema + "." + ((CreateTable) statement).getTable()));
                } else if (statement instanceof Insert) {
                    ((Insert) statement).setTable(new Table(StringUtils.isNotEmpty(((Insert) statement).getTable().getSchemaName()) ? ((Insert) statement).getTable().getSchemaName() + "." + ((Insert) statement).getTable().getName() : schema + "." + ((Insert) statement).getTable()));
                } else if (statement instanceof Update) {
                    ((Update) statement).setTable(new Table(StringUtils.isNotEmpty(((Update) statement).getTable().getSchemaName()) ? ((Update) statement).getTable().getSchemaName() + "." + ((Update) statement).getTable().getName() : schema + "." + ((Update) statement).getTable()));
                } else if (statement instanceof Delete) {
                    ((Delete) statement).setTable(new Table(StringUtils.isNotEmpty(((Delete) statement).getTable().getSchemaName()) ? ((Delete) statement).getTable().getSchemaName() + "." + ((Delete) statement).getTable().getName() : schema + "." + ((Delete) statement).getTable()));
                } else if (statement instanceof Drop) {
                    ((Drop) statement).setName(new Table(StringUtils.isNotEmpty(((Drop) statement).getName().getSchemaName()) ? ((Drop) statement).getName().getSchemaName() + "." + ((Drop) statement).getName().getName() : schema + "." + ((Drop) statement).getName()));
                } else if (statement instanceof Truncate) {
                    ((Truncate) statement).setTable(new Table(StringUtils.isNotEmpty(((Truncate) statement).getTable().getSchemaName()) ? ((Truncate) statement).getTable().getSchemaName() + "." + ((Truncate) statement).getTable().getName() : schema + "." + ((Truncate) statement).getTable()));
                } else if (statement instanceof Alter) {
                    ((Alter) statement).setTable(new Table(StringUtils.isNotEmpty(((Alter) statement).getTable().getSchemaName()) ? ((Alter) statement).getTable().getSchemaName() + "." + ((Alter) statement).getTable().getName() : schema + "." + ((Alter) statement).getTable()));
                } else if (statement instanceof Execute) {
                    ((Execute) statement).setName(((Execute) statement).getName().contains(".") ? ((Execute) statement).getName() : schema + "." + ((Execute) statement).getName());
                }
                sqls.add(statement.toString());
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }

        return Joiner.on(";\n").join(sqls);
    }


//    static Map<String, String> m = new HashMap();
//
//    public static String buildIncrSql(String sql, String incrTable, String incrField, String incStartValue, String incEndValue){
//        try {
//            generateIncrSql(sql, incrTable, incrField, incStartValue, incEndValue);
//        } catch (JSQLParserException e) {
//            log.error("增量SQL构建失败", e);
//            throw new DataFactoryException(ErrorCode.INCR_SQL_BUILD_ERROR);
//        }
//        for (String k : m.keySet()) {
//            String v = m.get(k);
//            sql = sql.replace(" "+k+" ", " "+v+" ");
//        }
//
//        return sql;
//    }
//
//    private static String generateIncrSql(String sql, String incrTable, String incrField, String incStartValue, String incEndValue) throws JSQLParserException {
//        Select select = (Select) CCJSqlParserUtil.parse(sql);
//        SelectBody selectBody = select.getSelectBody();
//        PlainSelect plainSelect = (PlainSelect) selectBody;
//        FromItem fromItem = plainSelect.getFromItem();
//        String tableName = "";
//        if (fromItem instanceof SubSelect) {
//            SubSelect table = (SubSelect) plainSelect.getFromItem();
//            generateIncrSql(table.getSelectBody().toString(), incrTable, incrField, incStartValue, incEndValue);
//        } else {
//            Table table = (Table) plainSelect.getFromItem();
//            tableName = table.getName();
//            if (tableName.equals(incrTable)) {
//                if (table.getAlias() == null) {
//                    m.put(tableName, "(select * from " + tableName + "  where " + incrField + ">'" + incStartValue + "' and " + incrField + "<='" + incEndValue + "') " + tableName);
//                } else {
//                    m.put(tableName, "(select * from " + tableName + "  where " + incrField + ">'" + incStartValue + "' and " + incrField + "<='" + incEndValue + "')");
//                }
//            }
//
//        }
//
//        if (plainSelect.getJoins() != null) {
//            for (Join join : plainSelect.getJoins()) {
//                if (join.getRightItem() instanceof SubSelect) {
//                    SubSelect table = (SubSelect) join.getRightItem();
//                    generateIncrSql(table.getSelectBody().toString(), incrTable, incrField, incStartValue, incEndValue);
//                } else {
//                    Table table = (Table) join.getRightItem();
//                    tableName = table.getName();
//                    if (tableName.equals(incrTable)) {
//                        if (table.getAlias() == null) {
//                            m.put(tableName, "(select * from " + tableName + "  where " + incrField + ">'" + incStartValue + "' and " + incrField + "<='" + incEndValue + "') " + tableName);
//                        } else {
//                            m.put(tableName, "(select * from " + tableName + "  where " + incrField + ">'" + incStartValue + "' and " + incrField + "<='" + incEndValue + "')");
//                        }
//                    }
//                }
//            }
//        }
//        return select.toString();
//    }


    public static void main(String[] args) throws JSQLParserException {
//        String build = SqlUtil.buildIncrSql("select * from (select * from m) m left join m_1 m1 on m1.id = m.id", "m", "id", "10", "100");
////        List<String> sqlTableNames = SqlUtil.getSqlTableNames("select a.c1,b.c2,c.* from (select * from tbl_a) a left join tbl_b b  on a.c3=b.c3 left join tbl_c c on a.c4=b.c4 where c.c5='tbl_d'");
//
//        System.out.println(build);

//        CreateTable parse = (CreateTable)CCJSqlParserUtil.parse("create table rrrrr(\n" +
//                "id varchar(10) primary key,\n" +
//                "name varchar(30) ,\n" +
//                "age numeric(8),\n" +
//                "create_time date,\n" +
//                "updata_time varchar(30)\n" +
//                ")");
//
////        System.out.println(parse.getTable());
//        parse.setTable(new Table(parse.getTable()+"xxxxxxx"));
//        System.out.println(parse.toString());

//        Insert insert = (Insert)CCJSqlParserUtil.parse("insert table rrrrr(id) values(\n" +
//                "xxxxx"+
//                ")");
//
////        System.out.println(parse.getTable());
//        insert.setTable(new Table(insert.getTable()+"xxxxxxx"));
//        System.out.println(insert.toString());

//        Delete delete = (Delete)CCJSqlParserUtil.parse("delete from rrrrr where 1=1");
//
////        System.out.println(parse.getTable());
//        delete.setTable(new Table(delete.getTable()+"xxxxxxx"));
//        System.out.println(delete.toString());

//        Drop drop = (Drop)CCJSqlParserUtil.parse("drop table rrrrr");
//
//        drop.setName(new Table(drop.getName()+"xxxxxxx"));
//        System.out.println(drop.toString());


        String aa = buildSqlWitchSchema("INSERT INTO device_attr_double_all (pk, deviceId, identifier, value, `time`, tenantId) VALUES('CKqeG4SnHUo', 'dv-conn-2', 'humidity', 30.0, '2022-12-02 09:17:18.715', 'system');", "aa");
        System.out.println(aa);


    }


}
